using System.Collections.Generic;
using System.ComponentModel;
using System;
using System.Collections;
using System.Diagnostics;
using System.IO;
using System.Reflection;

using CIndicators;
using CStock;
using CUtil;
using System.Data;


namespace CStock
{
    /// <summary>
    /// this class is abstract mainly because of the ID property, which sets also the path,which has to be either 
    /// Daily or Intraday
    /// </summary>
    public class ExcelReader : CUtil.Excel.ExcelDataReader
    {
       
        IPortfolios portfolios;
        System.Collections.Specialized.StringCollection portfolioNames;
        System.Collections.Specialized.StringCollection columnHeaders;
        System.Collections.Specialized.StringCollection tempColumnHeaders;

        ITradeEconomy tradeEconomy;
    
       
   
        public bool Read_C_book1_xls()
        {
            string filename = @"C:\book1.xls";
            if (ReadExcelFile_SetDataSet(filename))
            {
               return true;
            }
            else
                return false;

        }
        public bool ReadExcelFile_SetDataSet(string filename)
        {
            
            if (ReadExcelFile(filename))
            {
               return true;
            }
            else
                return false;

        }
        //-------------------
    
        private System.Collections.Specialized.StringCollection Get_Excel_ColumnHeaders(string tableName)
        {
            

            DataTable dt = m_dataSetExcel.Tables[tableName];
            if (dt == null)
            {
                System.Windows.Forms.MessageBox.Show("Worksheet not in Excel file: " + tableName );
                return null;
            }
            System.Collections.Specialized.StringCollection strColl = new System.Collections.Specialized.StringCollection();

            if (this.m_dataSetExcel != null)
            {
                if (dt.Rows.Count < 1)
                    return null;

                DataRow dr = dt.Rows[0];
                string shareName = dr[0].ToString();
                if (!String.IsNullOrEmpty(shareName ))
                {
                    for (int j = 0; j < dr.ItemArray.GetUpperBound(0); j++)
                    {
                        strColl.Add(dr[j] as string);

                    }
                }

            }
            return strColl;

        }

        private static void PrepareShare(IDataClass dc)
        {
            //prepare shareData
            dc.SeriesClose = new List<double>();
            dc.SeriesHigh = new List<double>();
            dc.SeriesLow = new List<double>();
            dc.SeriesOpen = new List<double>();
            dc.SeriesVolume = new List<System.Int64>();
            dc.SeriesTime = new List<DateTime>();

        }
        private static DateTime GetDateValue(object o)
        {
            string timeString = o.ToString();
            DateTime date = new DateTime(0);
            if (timeString.Length == 8)
            {
                date = new DateTime(0);
                try
                {
                    date = new DateTime(Convert.ToInt32(timeString.Substring(0, 4)),
                        Convert.ToInt32(timeString.Substring(4, 2)),
                        Convert.ToInt32(timeString.Substring(6, 2)));
                }
                catch (Exception exc)
                {
                    date = new DateTime(0);
                    System.Diagnostics.Debug.Assert(false, " Quote :- createDate(): Error creating Date \n ", exc.Message);
                    System.Diagnostics.Debug.WriteLine(" Quote :- createDate(): Error creating Date \n " + exc.Message);
                }
            }
            else
            {
                try
                {
                    date = Convert.ToDateTime(o);
                }
                catch (Exception ex)
                {
                    Debug.Assert(false, "Error reading date in Excel: " + timeString + " ; " + ex.Message);
                }
            }
            return date;







        }
        public IDataClass CreateDataClassAndReadValues(string shareName)
        {
            IDataClass dc = new DataClassDaily();
            dc.ID = shareName;
            
            ReadShareQuotes(shareName, dc);
            return dc;


        }
        private IDataClass ReadShareQuotes(string shareName, IDataClass dc)
        {
                PrepareShare(dc);
                
                try
                {
                    tempColumnHeaders = Get_Excel_ColumnHeaders(shareName);
                    if (tempColumnHeaders == null || tempColumnHeaders.Count < 1)
                        return null;

                    DataTable dt = m_dataSetExcel.Tables[shareName];
                    if (dt != null)
                    {
                        int indexOpen = tempColumnHeaders.IndexOf("Open");
                        int indexClose = tempColumnHeaders.IndexOf("Close");
                        int indexVolume = tempColumnHeaders.IndexOf("Volume");
                        int indexTime = tempColumnHeaders.IndexOf("Time");
                        int indexHigh = tempColumnHeaders.IndexOf("High");
                        int indexLow = tempColumnHeaders.IndexOf("Low");

                        int indexEquity = tempColumnHeaders.IndexOf("Equity");
                        int indexEarningPerShare = tempColumnHeaders.IndexOf("EarningPerShare");
                        int indexCashFlow = tempColumnHeaders.IndexOf("CashFlow");
                        int indexReturnOfInvestment = tempColumnHeaders.IndexOf("ReturnOfInvestment");




                        for (int i = 1; i < dt.Rows.Count; i++)
                        {
                            DataRow dr = dt.Rows[i];
                            //set only if time value is valid + Quote close is there ...

                            object oDateTime = dr[indexTime];
                            object oClose = dr[indexClose];
                            DateTime dateCurrentRead;
                            object o = null;
                            if (oDateTime != null && oDateTime.ToString() != string.Empty && oClose != null && oClose.ToString() != string.Empty)
                            {
                                //DateTime f = Convert.ToDateTime(o);
                                dateCurrentRead = GetDateValue(oDateTime);
                                double f1 = Convert.ToDouble(oClose.ToString(), CUtil.Conversions.CultureAmerican);
                                //dc.SeriesOpen.Add((double)o);
                                dc.SeriesClose.Add(f1);
                                dc.SeriesTime.Add(dateCurrentRead);

                                //now set also all the others!
                                o = dr[indexOpen];
                                if (o != null && o.ToString() != string.Empty)
                                {
                                    f1 = Convert.ToDouble(o.ToString(), CUtil.Conversions.CultureAmerican);
                                    //dc.SeriesOpen.Add((double)o);
                                    dc.SeriesOpen.Add(f1);
                                }
                                o = dr[indexHigh];
                                if (o != null && o.ToString() != string.Empty)
                                {
                                    f1 = Convert.ToDouble(o, CUtil.Conversions.CultureAmerican);
                                    //dc.SeriesOpen.Add((double)o);
                                    dc.SeriesHigh.Add(f1);
                                }

                                o = dr[indexLow];
                                if (o != null && o.ToString() != string.Empty)
                                {
                                    f1 = Convert.ToDouble(o, CUtil.Conversions.CultureAmerican);
                                    //dc.SeriesOpen.Add((double)o);
                                    dc.SeriesLow.Add(f1);
                                }

                                o = dr[indexVolume];
                                if (o != null && o.ToString() != string.Empty)
                                {
                                    int i1 = Convert.ToInt32(o);
                                    //dc.SeriesOpen.Add((double)o);
                                    dc.SeriesVolume.Add(i1);
                                }

                            }


                            if (oDateTime != null && oDateTime.ToString() != string.Empty)
                            {


                                dateCurrentRead = GetDateValue(oDateTime);
                                //only for shares we have these values - not important for Bonds
                                if (indexEquity > -1)
                                {
                                    o = dr[indexEquity];
                                    if (o != null && o.ToString() != string.Empty)
                                    {
                                        double f1 = Convert.ToDouble(o, CUtil.Conversions.CultureAmerican);
                                        //dc.SeriesOpen.Add((double)o);
                                        dc.Equity.Add(dateCurrentRead, f1);
                                    }
                                    o = dr[indexEarningPerShare];
                                    if (o != null && o.ToString() != string.Empty)
                                    {
                                        double f1 = Convert.ToDouble(o, CUtil.Conversions.CultureAmerican);
                                        //dc.SeriesOpen.Add((double)o);
                                        dc.EarningPerShare.Add(dateCurrentRead, f1);
                                    }
                                    o = dr[indexCashFlow];
                                    if (o != null && o.ToString() != string.Empty)
                                    {
                                        double f1 = Convert.ToDouble(o, CUtil.Conversions.CultureAmerican);
                                        //dc.SeriesOpen.Add((double)o);
                                        dc.CashFlow.Add(dateCurrentRead, f1);
                                    }
                                    o = dr[indexReturnOfInvestment];
                                    if (o != null && o.ToString() != string.Empty)
                                    {
                                        double f1 = Convert.ToDouble(o, CUtil.Conversions.CultureAmerican);
                                        //dc.SeriesOpen.Add((double)o);
                                        dc.ReturnOfInvestment.Add(dateCurrentRead, f1);
                                    }


                                }



                                //Debug.WriteLine(i.ToString() + "Read excel ");


                            }

                        }
                    }
                    Debug.WriteLine(shareName + " -- DataClass read - quotes created");
                    return dc;
                }
                catch (Exception ex)
                {
                    System.Windows.Forms.MessageBox.Show("CreateDataClassAndReadValues: Error reading share values for " + shareName + "; " + ex.Message);
                    return dc;

                }
        }
         
        
       
        public IDataItems GetDataItems_CreatePortfolio(string portfolioNameShares)
        {
            
            PortfolioFactory pf = new PortfolioFactory();
            pf.Create(portfolioNameShares);
            Portfolio myportfolio = pf.StockObjectBase as Portfolio;


            IDataItems dataItems = null;
            if(GetPortfolioContent(myportfolio))
                dataItems = myportfolio.DataItems;

            return dataItems;


        }
        private bool AddDataItemToPortfolio(DataTable dt, IPortfolio portf, string shareID)
        {
            IDataItems dataItems = new DataItems();
            IDataItem di;
            if (dt == null)
            {
                System.Windows.Forms.MessageBox.Show("Portfolio missing in input file");
                return false;
            }
            if (dt != null)
            {

                for (int i = 1; i < dt.Rows.Count; i++)
                {
                    di = new DataItem();
                    di.Parent = portf;

                    DataRow dr = dt.Rows[i];
                    shareID = dr[0].ToString();
                    if (!String.IsNullOrEmpty(shareID))
                    {
                        //dcf.Create(shareID);
                        PopulateDataItem(dr, di, shareID);

                        //di.Serialize();
                        dataItems.Items.Add(di);
                        portf.DataItems.Items.Add(di);

                    }
                    else
                    {
                        break;

                    }
                }
            }
            return true;
        }
        public bool GetPortfolioContent(IPortfolio portf)
        {
            
            string shareID = string.Empty;
            //IDataItem di;
            try
            {
                this.columnHeaders = Get_Excel_ColumnHeaders(portf.Name);

                if (columnHeaders == null || columnHeaders.Count < 1)
                {
                    Debug.Assert(false, "Error in Excel file - no column headers");
                    return false;
                }


                //DataClassFactory dcf = new DataClassFactory();
                //di = new DataItem();

                DataTable dt = m_dataSetExcel.Tables[portf.Name];



                AddDataItemToPortfolio(dt, portf, shareID);
                portf.Serialize();
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show(shareID + "GetDataItems_CreatePortfolio:  --- Error reading share - please check syntax of Excel worksheet " + ex.Message);
                return true;

            }


            return true;


        }
        private bool PopulateDataItem(DataRow dr, IDataItem di, string shareName)
        {
            try
            {
                IDataClassDaily dc = new DataClassDaily();
                di.DataClass = dc;
                dc.Parent = di;
                object o = null;
                o = FindObjectFromRow(dr, "ID");
                if (o != null)
                {
                   di.ID = o.ToString();
                }

                o = FindObjectFromRow(dr, "Name");
                if (o != null)
                {
                    shareName = o.ToString();
                    dc.ID = di.ID;
                }
                o = FindObjectFromRow(dr, "Symbol");
                if (o != null)
                {
                    di.Symbol = o.ToString();
                }
                o = FindObjectFromRow(dr, "BuyDate");
                if (o != null)
                {
                    DateTime dat = Convert.ToDateTime(o.ToString(), CUtil.Conversions.CultureAmerican);

                    //DateTime dt = DateTime.FromFileTimeUtc(l);
                    //DateTime dt = DateTime.FromOADate(l);
                    di.BuyDate = dat;
                }

                o = FindObjectFromRow(dr, "Number");
                if (o != null)
                {
                    double f = Convert.ToDouble(o.ToString(), CUtil.Conversions.CultureAmerican);
                    di.Number = Convert.ToInt32(f);
                }
                o = FindObjectFromRow(dr, "BuyQuote");
                if (o != null)
                {
                    di.BuyQuote = Convert.ToDouble(o, CUtil.Conversions.CultureAmerican);
                }
              
                o = FindObjectFromRow(dr, "Exchange");
                if (o != null)
                {
                    di.Exchange = o.ToString();
                }
                o = FindObjectFromRow(dr, "TotalNumberOverall");
                if (o != null)
                {
                    double f = Convert.ToDouble(o.ToString(), CUtil.Conversions.CultureAmerican);
                    di.DataClass.TotalNumberOverall = Convert.ToInt32(f, CUtil.Conversions.CultureAmerican);
                }
                o = FindObjectFromRow(dr, "BondType");
                if (o != null)
                {
                    if (o.ToString() == "TRUE")
                        di.DataClass.BondType = true;
                    o = FindObjectFromRow(dr, "BondInterestRate");
                    if (o != null)
                    {
                        di.DataClass.BondInterestRate = Convert.ToDouble(o, CUtil.Conversions.CultureAmerican);

                    }
                    o = FindObjectFromRow(dr, "BondIssuePrice");
                    if (o != null)
                    {
                        di.DataClass.BondIssuePrice = Convert.ToDouble(o, CUtil.Conversions.CultureAmerican);

                    }
                    o = FindObjectFromRow(dr, "BondIssueDate");
                    if (o != null)
                    {
                        DateTime dat = Convert.ToDateTime(o.ToString(), CUtil.Conversions.CultureAmerican);


                        di.DataClass.BondIssueDate = dat;
                    }
                    o = FindObjectFromRow(dr, "BondExpiryDate");
                    if (o != null)
                    {
                        DateTime dat = Convert.ToDateTime(o.ToString(), CUtil.Conversions.CultureAmerican);

                        di.DataClass.BondExpiryDate = dat;
                    }
                }





                return di.Serialize();
            }
            catch (Exception e)
            {
                Debug.Assert(false, "Error setting data item value for " + shareName, e.Message);
                return false;
            }

        }
        public static bool GetShareData(IDataItem mydataItem, string shareDirectoryPath)
        {
            string shareFileName = shareDirectoryPath + "\\StockData_" + mydataItem.ID + ".xls";
            ExcelReader excelReader = new ExcelReader();
            if (excelReader.ReadExcelFile_SetDataSet(shareFileName))
                return excelReader.GetShareData_Helper(mydataItem);
            else
                return false;


            
        }
        public bool GetShareData_Helper(IDataItem mydataItem)
        {

            try
            {
                string shareName = string.Empty;
                shareName = mydataItem.ID;
                IDataClass dc = mydataItem.DataClass;
                

                //DataClassFactory dcf = new DataClassFactory();

                // 

                //read share values only if we have a table for it
                DataTable dt = m_dataSetExcel.Tables[shareName];
                Debug.Assert(dt != null, "Data Table not set for this share " + mydataItem.ID);

                if (dt != null)
                {

                    if (!String.IsNullOrEmpty(shareName))
                    {



                        ReadShareQuotes(shareName, dc);
                       
                        
                        dc.ID = shareName;
                        dc.Parent = mydataItem;
                        //mydataItem.DataClass = dc;

                        mydataItem.DataClass.Serialize();

                        Debug.WriteLine("-------- Serialized : " + shareName);
                       


                    }
                }
                return true;
            }
            catch (Exception ex)
            {
                Debug.Assert(false, "Error getting share data " + mydataItem.ID + "; " + ex.Message);
                return false;

            }

        }
        public static bool GetQuotes_And_Serialize(IDataItems dataItems, string shareDirectoryPath)
        {
            string myId = string.Empty;
            
            try
            {
                

                for (int i = 0; i < dataItems.Count; i++)
                {

                    IDataItem di = dataItems[i];
                    myId = di.ID;
                    GetShareData(di, shareDirectoryPath);
        



                }

            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show(myId + " Get_Quotes_And_Serialize: --- Error reading share - please check syntax of Excel worksheet " + ex.Message);
                return false;

            }


            return true;


        }
       
        public ITradeEconomy GetEconomy_WithoutTraders_Etc()
        {
            DataTable dt = m_dataSetExcel.Tables["CentralBank"];
            this.columnHeaders = Get_Excel_ColumnHeaders("CentralBank");
            tradeEconomy = new TradeEconomy();
            Indicator.StaticApplicationPath = ProgramContext.Instance.Configuration.Directories.ApplicationPath;
            if (dt != null)
            {
                
                for (int i = 0; i < dt.Rows.Count; i++)
                {

                    DataRow dr = dt.Rows[i];
                    string str1 = dr[0].ToString();
                    if (str1 != string.Empty && str1 != "ID")
                    {
                        
                        object o = dr[columnHeaders.IndexOf("ID")];
                        if (o != null && o.ToString() != string.Empty)
                            tradeEconomy.ID = o.ToString();

                
                        o = dr[columnHeaders.IndexOf("Money_Current")];
                        if (o != null && o.ToString() != string.Empty)
                            tradeEconomy.Money_Current = Convert.ToDouble(o.ToString(), CUtil.Conversions.CultureAmerican);

                        o = dr[columnHeaders.IndexOf("Date_Start")];
                        if (o != null && o.ToString() != string.Empty)
                        {
                            DateTime dat = Convert.ToDateTime(o.ToString(), CUtil.Conversions.CultureAmerican); 
                            
                            //long l = Convert.ToInt64(o);
                            //DateTime dt = DateTime.FromFileTimeUtc(l);
                            //DateTime dat = DateTime.FromOADate(l);
                            
                            tradeEconomy.Date_Start = dat;
                            tradeEconomy.Date_Current = dat;


                        }
                        tradeEconomy.TradeSystem1 = GetTradeSystemTemplate(dr, "TradeSystem1");
                        tradeEconomy.TradeSystem2 = GetTradeSystemTemplate(dr, "TradeSystem2");
                        tradeEconomy.TradeSystem3 = GetTradeSystemTemplate(dr, "TradeSystem3");
                        tradeEconomy.TradeSystem4 = GetTradeSystemTemplate(dr, "TradeSystem4");




                    }


                }
            }



            //CStock.TradeEconomy.DeserializeEconomyStatus(tradeEconomy);
            return tradeEconomy;
            
        }
        public IOrders GetOrders()
        {
            DataTable dt = m_dataSetExcel.Tables["Orders"];
            this.columnHeaders = Get_Excel_ColumnHeaders("Orders");
            IOrders orders = new Orders();
            if (dt != null)
            {

                for (int i = 0; i < dt.Rows.Count; i++)
                {

                    DataRow dr = dt.Rows[i];
                    string str1 = dr[0].ToString();
                    if (str1 != string.Empty && str1 != "TraderID")
                    {
                        IOrder order = new Order();

                        order.TraderID = str1;
                        
                        

                        
                        
                        object o = dr[columnHeaders.IndexOf("ID")];
                        if(o != null && o.ToString() != string.Empty)
                            order.ID = o.ToString();

                        o = dr[columnHeaders.IndexOf("BidOnIndicator")];
                        if (o != null && o.ToString() != string.Empty)
                            order.BidOnIndicator = o.ToString();
                        o = dr[columnHeaders.IndexOf("BidOnIndicatorValue")];
                        if (o != null && o.ToString() != string.Empty)
                            order.BidOnIndicatorValue = Convert.ToDouble(o.ToString(), CUtil.Conversions.CultureAmerican);
                        

                        o = dr[columnHeaders.IndexOf("Bid")];
                        if (o != null && o.ToString() != string.Empty)
                            order.Bid = Convert.ToDouble(o.ToString(), CUtil.Conversions.CultureAmerican);

                        o = dr[columnHeaders.IndexOf("OrderType")];

                        string str = o.ToString();
                        switch (str)
                        {
                            case "Buy_Asset":
                                {
                                    order.OrderType = OrderType.Buy_Asset;
                                    break;
                                }
                            case "Sell_Asset":
                                {
                                    order.OrderType = OrderType.Sell_Asset;
                                    break;
                                }
                            case "Buy_Deposit":
                                {
                                    order.OrderType = OrderType.Buy_Deposit;
                                    break;
                                }
                            case "Sell_Deposit":
                                {
                                    order.OrderType = OrderType.Sell_Deposit;
                                    break;
                                }
                            case "Buy_Stock":
                                {
                                    order.OrderType = OrderType.Buy_Stock;
                                    break;
                                }
                            case "Sell_Stock":
                                {
                                    order.OrderType = OrderType.Sell_Stock;
                                    break;
                                }
                            case "Buy_Bond":
                                {
                                    order.OrderType = OrderType.Buy_Bond;
                                    break;
                                }
                            case "Sell_Bond":
                                {
                                    order.OrderType = OrderType.Sell_Bond;
                                    break;
                                }
                            case "Buy_Asset_CB":
                                {
                                    order.OrderType = OrderType.Buy_Asset_CB;
                                    break;
                                }
                            case "Sell_Asset_CB":
                                {
                                    order.OrderType = OrderType.Sell_Asset_CB;
                                    break;
                                }
                            case "Buy_Deposit_CB":
                                {
                                    order.OrderType = OrderType.Buy_Deposit_CB;
                                    break;
                                }
                            case "Sell_Deposit_CB":
                                {
                                    order.OrderType = OrderType.Sell_Deposit_CB;
                                    break;
                                }
                           
                            default:
                                {
                                    System.Windows.Forms.MessageBox.Show("Order type not set - default is : BuyStock");
                                    order.OrderType = OrderType.Buy_Asset;
                                    break;
                                }

                        }
                        o = dr[columnHeaders.IndexOf("DateOrder")];
                        if (o != null && o.ToString() != string.Empty)
                        {
                            
                            //DateTime dt = DateTime.FromFileTimeUtc(l);
                            DateTime dat = Convert.ToDateTime(o.ToString(), CUtil.Conversions.CultureAmerican); 
                            order.DateOrder = dat;
                        }
                        o = dr[columnHeaders.IndexOf("DateDue")];
                        if (o != null && o.ToString() != string.Empty)
                        {

                            DateTime dat = Convert.ToDateTime(o.ToString(), CUtil.Conversions.CultureAmerican); 
                            
                            order.DateDue = dat;
                        }


                        o = dr[columnHeaders.IndexOf("NumberOfItems")];
                        order.NumberOfItems = Convert.ToInt32(o.ToString());
                        orders.Add(order);



                    }


                }
            }



            return orders;
        }
        public IAssets GetAssets()
        {
            DataTable dt = m_dataSetExcel.Tables["Assets"];
            this.columnHeaders = Get_Excel_ColumnHeaders("Assets");
            IAssets assets = new Assets();
            if (dt != null)
            {

                for (int i = 0; i < dt.Rows.Count; i++)
                {

                    DataRow dr = dt.Rows[i];
                    string str1 = dr[0].ToString();
                    if (str1 != string.Empty && str1 != "ID")
                    {
                        IAsset asset = new Asset();

                        asset.ID = str1;
                        object o = dr[columnHeaders.IndexOf("Name")];
                        if (o != null && o.ToString() != string.Empty)
                            asset.ID = o.ToString();
                        //string strDate1 = Convert.ToString(dr[5].ToString());
                        o = dr[columnHeaders.IndexOf("OwnerID")];
                        if (o != null && o.ToString() != string.Empty)
                            asset.OwnerID = o.ToString();

                       
                        o = dr[columnHeaders.IndexOf("DateBuy")];
                        if (o != null && o.ToString() != string.Empty)
                        {

                            DateTime dat = Convert.ToDateTime(o.ToString(), CUtil.Conversions.CultureAmerican); 
                            
                            asset.DateBuy = dat;
                        }

                        o = dr[columnHeaders.IndexOf("Value_Initial")];
                        if (o != null && o.ToString() != string.Empty)
                            asset.Value_Initial = Convert.ToDouble(o.ToString(), CUtil.Conversions.CultureAmerican);

                        o = dr[columnHeaders.IndexOf("ValueBuy")];
                        if (o != null && o.ToString() != string.Empty)
                            asset.ValueBuy = Convert.ToDouble(o.ToString(), CUtil.Conversions.CultureAmerican);


                        o = dr[columnHeaders.IndexOf("Value_Current")];
                        if (o != null && o.ToString() != string.Empty)
                            asset.Value_Current = Convert.ToDouble(o.ToString(), CUtil.Conversions.CultureAmerican);



                        o = dr[columnHeaders.IndexOf("Number")];
                        if (o != null && o.ToString() != string.Empty)
                            asset.Number = Convert.ToInt32(o.ToString());


                        assets.Items.Add(asset);


                    }


                }
            }
            


            return assets;
        }
        public IDeposits GetDeposits()
        {
            DataTable dt = m_dataSetExcel.Tables["Deposits"];
            this.columnHeaders = Get_Excel_ColumnHeaders("Deposits");
            IDeposits deposits = new Deposits();
            if (dt != null)
            {

                for (int i = 0; i < dt.Rows.Count; i++)
                {

                    DataRow dr = dt.Rows[i];
                    string str1 = dr[0].ToString();
                    if (str1 != string.Empty && str1 != "ID")
                    {
                        IDeposit deposit = new Deposit();

                        deposit.ID = str1;
                        //object o = dr[columnHeaders.IndexOf("Name")];
                        //if (o != null && o.ToString() != string.Empty)
                        //    deposit.Name = o.ToString();

                        object o = dr[columnHeaders.IndexOf("DateContract")];
                        if (o != null && o.ToString() != string.Empty)
                        {

                            DateTime dat = Convert.ToDateTime(o.ToString(), CUtil.Conversions.CultureAmerican); 
                            
                            deposit.DateContract = dat;
                        }

                        o = dr[columnHeaders.IndexOf("DateExpire")];
                        if (o != null && o.ToString() != string.Empty)
                        {

                            DateTime dat = Convert.ToDateTime(o.ToString(), CUtil.Conversions.CultureAmerican); 
                            
                            deposit.DateExpire = dat;
                        }
                        o = dr[columnHeaders.IndexOf("DateBuy")];
                        if (o != null && o.ToString() != string.Empty)
                        {

                            DateTime dat = Convert.ToDateTime(o.ToString(), CUtil.Conversions.CultureAmerican); 
                            
                            deposit.DateBuy = dat;
                        }
                        o = dr[columnHeaders.IndexOf("Value_Initial")];
                        if (o != null && o.ToString() != string.Empty)
                            deposit.ValueBuy = Convert.ToDouble(o.ToString(), CUtil.Conversions.CultureAmerican);

                        o = dr[columnHeaders.IndexOf("Value_Current")];
                        if (o != null && o.ToString() != string.Empty)
                            deposit.Value_Current = Convert.ToDouble(o.ToString(), CUtil.Conversions.CultureAmerican);


                        //string strDate1 = Convert.ToString(dr[5].ToString());
                        o = dr[columnHeaders.IndexOf("OwnerID")];
                        if (o != null && o.ToString() != string.Empty)
                            deposit.OwnerID = o.ToString();

                        //string strDate1 = Convert.ToString(dr[5].ToString());
                        o = dr[columnHeaders.IndexOf("InterestRateInitial")];
                        if (o != null && o.ToString() != string.Empty)
                            deposit.InterestRateInitial = Convert.ToDouble(o.ToString(), CUtil.Conversions.CultureAmerican);

                        o = dr[columnHeaders.IndexOf("InterestRateCurrent")];
                        if (o != null && o.ToString() != string.Empty)
                            deposit.InterestRateCurrent = Convert.ToDouble(o.ToString(), CUtil.Conversions.CultureAmerican);



                        


                        o = dr[columnHeaders.IndexOf("Number")];
                        if (o != null && o.ToString() != string.Empty)
                            deposit.Number = Convert.ToInt32(o.ToString());


                        deposits.Items.Add(deposit);

                    }


                }
            }



            



            return deposits;
        }
        private object FindObjectFromRow(DataRow dr, string columnString)
        {
            int i = columnHeaders.IndexOf(columnString);
            if (i > 0)
            {
                object o = dr[columnHeaders.IndexOf(columnString)];
                if (o != null && o.ToString() != string.Empty)
                {
                    return o;
                }
                else
                    return null;
            }
            else
            {
                return null;
            }

        }
        public ITraders GetTraders()
        {
            DataTable dt = m_dataSetExcel.Tables["Traders"];
            this.columnHeaders = Get_Excel_ColumnHeaders("Traders");
            ITraders traders = new Traders();
            if (dt != null)
            {

                for (int i = 0; i < dt.Rows.Count; i++)
                {

                    DataRow dr = dt.Rows[i];
                    string str1 = dr[0].ToString();
                    if (str1 != string.Empty && str1 != "ID")
                    {
                        ITrader trader = new Trader();
                        object o = dr[columnHeaders.IndexOf("ID")];
                        if (o != null && o.ToString() != string.Empty)
                            trader.ID = o.ToString();

                        o = FindObjectFromRow(dr, "Money_Current");
                        if (o != null)
                        {
                            trader.Money_Current = Convert.ToDouble(o.ToString(), CUtil.Conversions.CultureAmerican);

                        }

                        o = FindObjectFromRow(dr, "TradeBreaks");
                        if (o != null)
                        {
                            trader.TradeBreaks = Convert.ToInt32(o.ToString(), CUtil.Conversions.CultureAmerican);

                        }
                        o = FindObjectFromRow(dr, "TradeStart");
                        if (o != null)
                        {
                            DateTime dat = Convert.ToDateTime(o.ToString(), CUtil.Conversions.CultureAmerican);
                            trader.TradeStart = dat;
                        }

                        o = FindObjectFromRow(dr, "TradeEnd");
                        if (o != null)
                        {
                            trader.TradeEnd = o.ToString();
                        }

                       


                        
                        trader.TradeSystem1 = GetTradeSystemTemplate(dr, "TradeSystem1");
                        trader.TradeSystem2 = GetTradeSystemTemplate(dr, "TradeSystem2");
                        trader.TradeSystem3 = GetTradeSystemTemplate(dr, "TradeSystem3");
                        trader.TradeSystem4 = GetTradeSystemTemplate(dr, "TradeSystem4");


                        traders.Add(trader);

                       

                    }


                }
            }



            return traders;

        }
        private ITradeSystem GetTradeSystemTemplate(DataRow dr, string strTradeSystem)
        {
            object o = null;
            ITradeSystem ts = null;


            int iIndex = columnHeaders.IndexOf(strTradeSystem);
            if (iIndex > -1)
            {
                o = dr[iIndex];
                if (o != null && o.ToString() != string.Empty)
                {
                    string strTradeSystemID = o.ToString();
                    string filePath = ProgramContext.Instance.Configuration.Directories.TradeSystemsTemplatesPath + "\\" + strTradeSystemID + ".xml";

                    ts = CStock.TradeSystem.GetTradeSystemTemplate(filePath);
                    //if (ts != null)
                    //{//get money
                    //    o = dr[columnHeaders.IndexOf(strTradeSystem + "_Money")];

                    //    if (o != null && o.ToString() != string.Empty)
                    //    {

                    //        ts.Money_Initial = Convert.ToDouble(o.ToString(), CUtil.Conversions.CultureAmerican);


                    //    }
                    //}
                    //else
                    //{
                    //    System.Windows.Forms.MessageBox.Show("Could not find trade system " + filePath);
                    //}
                    
                }
                return ts;

                
            }
            return null;
        }
        public IPortfolios GetPortfolios(string shareDirectoryPath)
        {
            try
            {
                DataTable dt = m_dataSetExcel.Tables["Portfolios"];
                System.Collections.Specialized.StringCollection myPortfolioColumnHeaders = Get_Excel_ColumnHeaders("Portfolios");


                if (dt != null)
                {
                    this.portfolioNames = new System.Collections.Specialized.StringCollection();
                    portfolios = new Portfolios();
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {

                        DataRow dr = dt.Rows[i];
                        string str1 = dr[0].ToString();
                        if (str1 != string.Empty && str1 != "ID")
                        {
                            IPortfolio port = new Portfolio();
                            port.ID = str1;

                            object o = dr[myPortfolioColumnHeaders.IndexOf("OwnerID")];
                            if (o != null)
                                port.OwnerID = o.ToString();



                            IDataItems dataItems = null;
                            if (GetPortfolioContent(port))
                                dataItems = port.DataItems;
                            //read share values only when share worksheet is there
                            if (dataItems != null && dataItems.Count > 0)
                            {
                                Debug.Assert(GetQuotes_And_Serialize(dataItems, shareDirectoryPath));
                            }

                            portfolios.Items.Add(port);
                            portfolioNames.Add(str1);

                        }


                    }
                }


                if (portfolios != null && portfolios.Items.Count > 0)
                    return portfolios;
                else
                    return null;
            }
            catch (Exception ex)
            {
                Debug.Assert(false, "Error in setting Portfolios: " + ex.Message);
                return null;

            }

        }
        public ITradeEconomy TradeEconomy
        {
            get
            {
                return this.tradeEconomy;
            }
        }
        public ITradeEconomy GetEconomy(string shareDirectoryPath)
        {
            try
            {
                tradeEconomy = this.GetEconomy_WithoutTraders_Etc();
                
                tradeEconomy.Traders = this.GetTraders();

                //....................................

                tradeEconomy.Portfolios = this.GetPortfolios(shareDirectoryPath);
                tradeEconomy.Portfolio_Selected = tradeEconomy.Portfolios[0];


               
                //-------------------------------------------------
                //Creates Portfolios with only one share ! 

                
                return tradeEconomy;
            }
            catch (Exception e)
            {
                Debug.Assert(false, "Error getting TradeEconomy", e.Message);
                return null;

            }
            
            

        }

        public static bool UpdateShares(ITradeEconomy tradeEconomy, string path)
        {
            for (int i = 0; i < tradeEconomy.Portfolios.Items.Count; i++)
            {
                IPortfolio port = tradeEconomy.Portfolios[i];
                for (int j = 0; j < port.DataItems.Count; j++)
                {
                    IDataClass dc = port.DataItems[i].DataClass;
                    string shareFileName = path + "\\StockData_" + dc.ID + ".xls";
                    CUtil.Excel.ExcelWriter excelWriter = new CUtil.Excel.ExcelWriter();
                    if(System.IO.File.Exists(shareFileName))
                    {
                        //CUtil.IOUtil strSystem.IO.File.Copy(shareFileName + "");
                        System.IO.File.Delete(shareFileName);
                    }


                    excelWriter.CreateFile(shareFileName);

                  
                }

            }
            return true;



        }


    }
}
